﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using NUnit.Framework;
using ScrappyDB.Linq;
using ScrappyDB.Attributes;
using ScrappyDB.BaseClasses;

namespace ScrappyDB.UnitTests.AwsQuery101Examples
{
    /************************************************************************************
    *   Query 101: Building Amazon SimpleDB Queries
    *   Comparison Operators Examples     
    *   Source document: http://aws.amazon.com/articles/1231
    *************************************************************************************/

    [TestFixture]
    public class ComparisonOperatorTests
    {
        /************************************************************************************
         * Comparison Operator: = 
         * Description:         Attribute value equals the specified constant. 
         * Example SQL:         select * from mydomain where city = 'Seattle'
         *                      select * from mydomain where city = 'Seattle' or city = 'Portland'
         ************************************************************************************/
        [Test]
        public void EqualsTest()
        {
            const string expectedSql1 = "select * from `mydomain` where `city` = 'Seattle'";
            const string expectedSql2 = "select * from `mydomain` where `city` = 'Seattle' or `city` = 'Portland'";

            var context = new TestEntityContext();

            var queryable1 = from a in context.TestEntitys where a.city == "Seattle" select a;
            var sql1 = context.ToSql<mydomain>(queryable1.Expression);
            Assert.AreEqual(expectedSql1, sql1);

            var queryable2 = from a in context.TestEntitys where a.city == "Seattle" | a.city == "Portland" select a;
            var sql2 = context.ToSql<mydomain>(queryable2.Expression);
            Assert.AreEqual(expectedSql2, sql2);
        }

        /************************************************************************************
        * Comparison Operator: != 
        * Description:         Attribute value does not equal to the specified constant. 
        * Example SQL:         select * from mydomain where name != 'John'
        *                      select * from mydomain where name != 'John' and name != 'Humberto'
        ************************************************************************************/
        [Test]
        public void NotEqualsTest()
        {
            const string expectedSql1 = "select * from `mydomain` where `name` != 'John'";
            const string expectedSql2 = "select * from `mydomain` where `name` != 'John' and `name` != 'Humberto'";

            var context = new TestEntityContext();

            var queryable1 = from a in context.TestEntitys where a.name != "John" select a;
            var sql1 = context.ToSql<mydomain>(queryable1.Expression);
            Assert.AreEqual(expectedSql1, sql1);

            var queryable2 = from a in context.TestEntitys where a.name != "John" & a.name != "Humberto" select a;
            var sql2 = context.ToSql<mydomain>(queryable2.Expression);
            Assert.AreEqual(expectedSql2, sql2);
        }


        /************************************************************************************
        * Comparison Operator: > 
        * Description:         Attribute value is greater than the specified constant. 
        * Example SQL:         select * from mydomain where weight > '0034' 
        ************************************************************************************/
        [Test]
        public void GreaterThanTestNumeric()
        {
            const string expectedSql1 = "select * from `mydomain` where `weight` > '10000000034'";

            var context = new TestEntityContext();

            var queryable1 = from a in context.TestEntitys where a.weight > 34 select a;
            var sql1 = context.ToSql<mydomain>(queryable1.Expression);
            Assert.AreEqual(expectedSql1, sql1);
        }

        [Test]
        public void GreaterThanTestString()
        {
            const string expectedSql1 = "select * from `mydomain` where `city` > 'Portland'";

            var context = new TestEntityContext();

            var queryable1 = from a in context.TestEntitys where a.city.GreaterThan("Portland") select a;
            var sql1 = context.ToSql<mydomain>(queryable1.Expression);
            Assert.AreEqual(expectedSql1, sql1);
        }

        /************************************************************************************
        * Comparison Operator: >= 
        * Description:         Attribute value is greater than or equal to the specified constant. 
        * Example SQL:         select * from mydomain where weight >= '065' 
        ************************************************************************************/
        [Test]
        public void GreaterThanEqualTestNumeric()
        {
            const string expectedSql1 = "select * from `mydomain` where `weight` >= '10000000065'";

            var context = new TestEntityContext();

            var queryable1 = from a in context.TestEntitys where a.weight >= 65 select a;
            var sql1 = context.ToSql<mydomain>(queryable1.Expression);
            Assert.AreEqual(expectedSql1, sql1);
        }

        [Test]
        public void GreaterThanEqualTestString()
        {
            const string expectedSql1 = "select * from `mydomain` where `city` >= 'Portland'";

            var context = new TestEntityContext();

            var queryable1 = from a in context.TestEntitys where a.city.GreaterThanEqual("Portland") select a;
            var sql1 = context.ToSql<mydomain>(queryable1.Expression);
            Assert.AreEqual(expectedSql1, sql1);
        }

        /************************************************************************************
        * Comparison Operator: < 
        * Description:         Attribute value is less than the specified constant. 
        * Example SQL:         select * from mydomain where weight < '0034' 
        ************************************************************************************/
        [Test]
        public void LessThanTestNumeric()
        {
            const string expectedSql1 = "select * from `mydomain` where `weight` < '10000000034'";

            var context = new TestEntityContext();

            var queryable1 = from a in context.TestEntitys where a.weight < 34 select a;
            var sql1 = context.ToSql<mydomain>(queryable1.Expression);
            Assert.AreEqual(expectedSql1, sql1);
        }

        [Test]
        public void LessThanTestString()
        {
            const string expectedSql1 = "select * from `mydomain` where `city` < 'Portland'";

            var context = new TestEntityContext();

            var queryable1 = from a in context.TestEntitys where a.city.LessThan("Portland") select a;
            var sql1 = context.ToSql<mydomain>(queryable1.Expression);
            Assert.AreEqual(expectedSql1, sql1);
        }

        /************************************************************************************
        * Comparison Operator: <= 
        * Description:         Attribute value is less than or equal to the specified constant. 
        * Example SQL:         select * from mydomain where year <= '2000' 
        ************************************************************************************/
        [Test]
        public void LessThanEqualTestNumeric()
        {
            const string expectedSql1 = "select * from `mydomain` where `weight` <= '10000000065'";

            var context = new TestEntityContext();

            var queryable1 = from a in context.TestEntitys where a.weight <= 65 select a;
            var sql1 = context.ToSql<mydomain>(queryable1.Expression);
            Assert.AreEqual(expectedSql1, sql1, "KNOWN BUG: Numeric values should all be getting padded.");
        }

        [Test]
        public void LessThanEqualTestString()
        {
            const string expectedSql1 = "select * from `mydomain` where `city` <= 'Portland'";

            var context = new TestEntityContext();

            var queryable1 = from a in context.TestEntitys where a.city.LessThanEqual("Portland") select a;
            var sql1 = context.ToSql<mydomain>(queryable1.Expression);
            Assert.AreEqual(expectedSql1, sql1);
        }

        /************************************************************************************
        * Comparison Operator: like 
        * Description:         Attribute value starts with the specified constant. 
        *                      Note: The like operator is similar to starts-with and only supports % at the end of the string.        
        * Example SQL:         select * from mydomain where author like 'Rob%' 
        ************************************************************************************/
        [Test]
        public void LikeStartsWithTest()
        {
            const string expectedSql1 = "select * from `mydomain` where `author` like 'Rob%'";

            var context = new TestEntityContext();

            var queryable1 = from a in context.TestEntitys where a.author.StartsWith("Rob") select a;

            var sql1 = context.ToSql<mydomain>(queryable1.Expression);
            Assert.AreEqual(expectedSql1, sql1);
        }

        [Test]
        public void LikeContainsTest()
        {
            const string expectedSql1 = "select * from `mydomain` where `author` like '%Rob%'";

            var context = new TestEntityContext();

            var queryable1 = from a in context.TestEntitys where a.author.Contains("Rob") select a;

            var sql1 = context.ToSql<mydomain>(queryable1.Expression);
            Assert.AreEqual(expectedSql1, sql1);
        }

        [Test]
        public void LikeEndsWithTest()
        {
            const string expectedSql1 = "select * from `mydomain` where `author` like '%Rob'";

            var context = new TestEntityContext();

            var queryable1 = from a in context.TestEntitys where a.author.EndsWith("Rob") select a;

            var sql1 = context.ToSql<mydomain>(queryable1.Expression);
            Assert.AreEqual(expectedSql1, sql1);
        }

        /************************************************************************************
        * Comparison Operator: not like
        * Description:         Attribute value does not start with the specified constant.         
        *                      Note: The not like operator is similar to does-not-start-with and only supports % at the end of the string.        
        * Example SQL:         select * from mydomain where author not like 'Henry%' 
        ************************************************************************************/
        [Test]
        public void NotLikeStartsWithTest()
        {
            Assert.Inconclusive("Not supported yet");
        }

        [Test]
        public void NotLikeContainsTest()
        {
            Assert.Inconclusive("Not supported yet");
        }

        [Test,Ignore("Not implemented yet")]
        public void NotLikeEndsWithTest()
        {
            const string expectedSql1 = "select * from `mydomain` where `author` like '%Rob'";

            var context = new TestEntityContext();

            var queryable1 = from a in context.TestEntitys where !a.author.EndsWith("Rob") select a;

            var sql1 = context.ToSql<mydomain>(queryable1.Expression);
            Assert.AreEqual(expectedSql1, sql1);
        }

        /************************************************************************************
        * Comparison Operator: between 
        * Description:         Attribute value falls within a range, including the start and end value. 
        * Example SQL:         select * from mydomain where year between '1998' and '2000' 
        ************************************************************************************/
        [Test]
        public void BetweenTest()
        {
            const string expectedSql1 = "select * from `mydomain` where `year` between '1998' and '2000'";

            var context = new TestEntityContext();

            var queryable1 = from a in context.TestEntitys where a.year.Between("1998", "2000") select a;

            var sql1 = context.ToSql<mydomain>(queryable1.Expression);
            Assert.AreEqual(expectedSql1, sql1);
        }

        [Test, Ignore]
        public void BetweenTestNumeric()
        {
            Assert.Inconclusive("Not supported yet");
        }

        /************************************************************************************
        * Comparison Operator: in 
        * Description:         Attribute value is equal to one of the specified constants. 
        * Example SQL:         select * from mydomain where year in('1998','2000','2003') 
        ************************************************************************************/
        [Test]
        public void InTest()
        {
            const string expectedSql1 = "select * from `mydomain` where `year` in ('1998','2000','2003')";

            var context = new TestEntityContext();

            var queryable1 = from a in context.TestEntitys where a.year.In( "1998", "2000", "2003" ) select a;

            var sql1 = context.ToSql<mydomain>(queryable1.Expression);
            Assert.AreEqual(expectedSql1, sql1);
        }

        /************************************************************************************
        * Comparison Operator: is null 
        * Description:         Attribute does not exist. If an item has the attribute with an empty string, it is not returned. 
        *                      Note: Due to performance issues, this operator is not recommended when most items have the specified attribute.        
        * Example SQL:         select * from mydomain where year is null 
        ************************************************************************************/
        [Test]
        public void IsNullTest()
        {
            const string expectedSql1 = "select * from `mydomain` where `year` is null";

            var context = new TestEntityContext();

            var queryable1 = from a in context.TestEntitys where a.year.IsNull() select a;

            var sql1 = context.ToSql<mydomain>(queryable1.Expression);
            Assert.AreEqual(expectedSql1, sql1);
        }

        /************************************************************************************
        * Comparison Operator: is not null Attribute value contains any value. 
        * Example SQL:         select * from mydomain where year is not null 
        ************************************************************************************/
        [Test]
        public void IsNotNullTest()
        {
            const string expectedSql1 = "select * from `mydomain` where `year` is not null";

            var context = new TestEntityContext();

            var queryable1 = from a in context.TestEntitys where a.year.IsNotNull() select a;

            var sql1 = context.ToSql<mydomain>(queryable1.Expression);
            Assert.AreEqual(expectedSql1, sql1);
        }

        /************************************************************************************
        * Comparison Operator: every()  
        * Description:         For multi-valued attributes, every attribute value must satisfy the constraint. 
        *                      Note: Due to the cost of running more complex queries, this operator is only recommended for multi-valued attributes.        
        * Example SQL:         select * from mydomain where every(keyword) = 'Book' 
        *                      select * from mydomain where every(keyword) like '***%'
        ************************************************************************************/
        [Test]
        public void EveryEqualsTest()
        {
            const string expectedSql1 = "select * from `mydomain` where every(`keyword`) = 'Book'";

            var context = new TestEntityContext();

            var queryable1 = from a in context.TestEntitys where a.keyword.Every("Book") select a;

            var sql1 = context.ToSql<mydomain>(queryable1.Expression);
            Assert.AreEqual(expectedSql1, sql1);
        }
        [Test]
        public void EveryLikeTest()
        {
            //select * from mydomain where every(keyword) like '***%'

            Assert.Inconclusive("Not supported yet");
        }

        /************************************************************************************
        * Comparison Operator: and (intersection)
        * Description:         Determine the intersection of item name sets   
        * Example SQL:         select * from mydomain where `first name` = 'John' and `last name` = 'Smith'
        ************************************************************************************/
        [Test]
        public void AndTest()
        {
            const string expectedSql1 = "select * from `mydomain` where `firstname` = 'John' and `lastname` = 'Smith'";

            var context = new TestEntityContext();

            var queryable1 = from a in context.TestEntitys where a.firstname == "John" & a.lastname == "Smith" select a;
            var sql1 = context.ToSql<mydomain>(queryable1.Expression);
            Assert.AreEqual(expectedSql1, sql1);
        }




    }
    public class mydomain : SdbEntity
    {
        public string id { get; set; }
        public string city { get; set; }
        public string name { get; set; }
        public string firstname { get; set; }
        public string lastname { get; set; }
        public int weight { get; set; }
        public string author { get; set; }
        public string year { get; set; }
        public List<string> keyword { get; set; }
    }

    public class TestEntityContext : SdbContext
    {
        public SdbSet<mydomain> TestEntitys { get; set; }
    }

}
